package com.bluesky.light.product;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.bluesky.light.category.Category;
import com.bluesky.light.util.DB;

/**
 * 這個源代碼文件是存放關於產品在數據庫中的增刪查改等操作的函數
 * 
 * @author
 * 
 */
public class ProductDAO {

	/**
	 * 獲得所有的產品
	 * 
	 * @param products
	 * @param lazy
	 * @return totalRecords;
	 */
	public int getProducts(List<Product> products, boolean lazy) {
		int totalRecords = -1;

		Connection conn = DB.getConn();
		Statement stmt = DB.getStatement(conn);
		String sql = "";
		if (lazy) {
			sql = "select * from t_product order by pdate desc";
		} else {
			// System.out.println("lazy and sql");
			sql = "select p.id productid, p.name pname, p.descr pdescr, p.cost, "
					+ "p.price, p.inven, p.pdate, p.categoryid, "
					+ "c.id categoryid, c.name cname, c.descr cdescr, c.pid, c.cno, c.grade "
					+ "from t_product p join t_category c on (p.categoryid = c.id) order by p.pdate desc";
		}
		ResultSet rs = DB.getResultSet(stmt, sql);

		String sqlCount = "select count(*) from t_product";
		Statement stmtCount = DB.getStatement(conn);
		ResultSet rsCount = DB.getResultSet(stmtCount, sqlCount);

		try {
			rsCount.next();
			totalRecords = rsCount.getInt(1);

			while (rs.next()) {
				// System.out.println("rs has next");
				Product p = null;
				if (lazy) {
					p = this.getProductFromRs(rs);
					// System.out.println("lazy is true");
				} else {
					p = new Product();
					p.setId(rs.getInt("productid"));
					// System.out.println(rs.getInt("productid"));
					p.setName(rs.getString("pname"));
					p.setDescr(rs.getString("pdescr"));
					p.setCost(rs.getDouble("cost"));
					p.setPrice(rs.getDouble("price"));

					p.setInven(rs.getInt("inven"));
					p.setPdate(rs.getTimestamp("pdate"));
					// System.out.println(rs.getTimestamp("pdate"));
					p.setCategoryId(rs.getInt("categoryid"));

					Category c = new Category();
					c.setId(rs.getInt("categoryid"));
					c.setPid(rs.getInt("pid"));
					c.setName(rs.getString("cname"));
					c.setDescr(rs.getString("cdescr"));
					c.setCno(rs.getInt("cno"));
					c.setGrade(rs.getInt("grade"));

					p.setCategory(c);
				}
				products.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rsCount);
			DB.close(stmtCount);
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}

		return totalRecords;

	}

	/**
	 * 根據產品的識別號在數據庫中刪除一個產品
	 * 
	 * @param id
	 */
	public void delete(int id) {
		Connection conn = null;
		Statement stmt = null;

		try {
			String sql = "delete from t_product where id = " + id;
			conn = DB.getConn();
			stmt = DB.getStatement(conn);
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(stmt);
			DB.close(conn);
		}
	}

	/**
	 * 根據產品的id獲得產品的資料
	 * 
	 * @param id
	 * @return
	 */
	public Product getProductById(int id) {
		Product p = null;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DB.getConn();
			String sql = "select * from t_product where id = " + id;
			stmt = DB.getStatement(conn);
			rs = DB.getResultSet(stmt, sql);

			if (rs.next()) {
				p = getProductFromRs(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return p;
	}

	/**
	 * 更新被指定的產品資料
	 * 
	 * @param p
	 */
	public void update(Product p) {
		Connection conn = DB.getConn();
		String sql = "update t_product set name = ?, descr = ?, inprice = ?, outprice = ?, inven = ?, categoryid = ? "
				+ "where id = ?";
		PreparedStatement pstmt = DB.prepare(conn, sql);
		try {
			pstmt.setString(1, p.getName());
			pstmt.setString(2, p.getDescr());
			pstmt.setDouble(3, p.getCost());
			pstmt.setDouble(4, p.getPrice());
			pstmt.setInt(5, p.getInven());
			pstmt.setInt(6, p.getCategoryId());
			pstmt.setInt(7, p.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	/**
	 * 從ResultSet中獲得產品的資料
	 * 
	 * @param rs
	 * @return
	 */
	private Product getProductFromRs(ResultSet rs) {
		Product p = null;
		try {
			p = new Product();
			p.setId(rs.getInt("id"));
			p.setName(rs.getString("name"));
			p.setDescr(rs.getString("descr"));
			p.setCost(rs.getDouble("cost"));
			p.setPrice(rs.getDouble("price"));
			p.setInven(rs.getInt("inven"));
			p.setPdate(rs.getTimestamp("pdate"));
			p.setCategoryId(rs.getInt("categoryid"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return p;
	}

	/**
	 * 插入一個新產品到數據庫
	 * 
	 * @param p
	 */
	public void add(Product p) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			String sql = "insert into t_product (name, descr, cost, price, inven, pdate, categoryid) values "
					+ "(?, ?, ?, ?, ?, ?, ?)";
			pstmt = DB.prepare(conn, sql);
			pstmt.setString(1, p.getName());
			pstmt.setString(2, p.getDescr());
			pstmt.setDouble(3, p.getCost());
			pstmt.setDouble(4, p.getPrice());
			pstmt.setInt(5, p.getInven());
			pstmt.setTimestamp(6, new Timestamp(p.getPdate().getTime()));
			pstmt.setInt(7, p.getCategoryId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	/**
	 * 根據產品的名字取出產品
	 * 
	 * @param name
	 * @return
	 */
	public List<Product> getProductsByName(String name) {
		Connection conn = null;
		String sql = "select * from t_product where name like '%" + name
				+ "%' or descr like '%" + name + "%'";
		Statement stmt = null;
		ResultSet rs = null;

		List<Product> products = new ArrayList<Product>();

		try {
			conn = DB.getConn();
			stmt = DB.getStatement(conn);
			rs = DB.getResultSet(stmt, sql);

			while (rs.next()) {
				Product p = getProductFromRs(rs);
				products.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return products;
	}
}
